﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Framework.Data.Common;
using Framework.Extensions;
namespace Framework.Data.SqlServer
{
    public class SqlServerQueryingImplementationDetails : DatabaseQueryingImplementationDetailsBase
    {
        public override bool SupportsPagedStatements
        {
            get { return true; }
        }

        public override bool SupportsUpsertStatement
        {
            get { return true; }
        }

        public override string GetPagedStatement(string objectName, string orderBy, int startingRecord, int recordCount, string columns = "*", string where = "")
        {
            if (!string.IsNullOrEmpty(where)) where += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "where " + where;
            var sql = string.Format("WITH _paged AS (SELECT TOP ({3} + {4}) {0}, ROW_NUMBER() OVER(ORDER BY {5}) AS _RowNum FROM {1} {2}) SELECT {0} FROM _paged WHERE _RowNum > {4} ORDER BY {5};", columns, objectName, where, recordCount, startingRecord, orderBy);
            return sql;
        }


        public override string GetUpsertStatement(string objectName, IEnumerable<string> keyColumnNames, IEnumerable<string> editableColumnNames)
        {
            var keyColumns = keyColumnNames.ToList();
            var keyColumnsCount = keyColumnNames.Count();
            var keysWhereClause = string.Join(" AND ", keyColumnsCount.ToList(x => keyColumns[x] + " = @" + x.ToString()));

            var updatedColumns = editableColumnNames.Where(x => !keyColumnNames.Contains(x)).ToList();
            var columnsCount = updatedColumns.Count();
            var columnsStatement = string.Join(", ", columnsCount.ToList(x => updatedColumns[x] + " = @" + (x + keyColumnsCount).ToString()));


            keyColumns.AddRange(updatedColumns);
            var insertedColumnsStatement = keyColumns.ToString(", ");
            var insertedValuesStatement = string.Join(", ", keyColumns.Count().ToList(x => "@" + x.ToString()));

            var sql = string.Format("if exists(select 1 from {0} where {1})", objectName, keysWhereClause);
            sql += string.Format("update {0} set {1} where {2}", objectName, columnsStatement, keysWhereClause);
            sql += " else ";
            sql += string.Format("insert into {0} ({1}) values({2})", objectName, insertedColumnsStatement, insertedValuesStatement);
            return sql;
        }
    }
}
